package asorm.db
{
	import flash.data.SQLStatement;
	import flash.data.SQLConnection;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	import asorm.db.event.LocalDatabaseEvent;
	import flash.events.EventDispatcher;
	import mx.collections.ArrayCollection;
	import flash.data.SQLResult;
	import flash.net.Responder;
	
	[Event(name="databaseOpened", type="util.db.event.LocalDatabaseEvent")]
	[Event(name="databaseSQLExecuted", type="util.db.event.LocalDatabaseEvent")]
	public class LocalDatabase extends EventDispatcher
	{
		public function LocalDatabase(name: String) {
			_dbName = name;
			_statementQueue = new ArrayCollection();
			_lastSQLStatement = null;
			_dbConnection = new SQLConnection();
			_dbFile = File.applicationStorageDirectory.resolvePath(_dbName + ".db");
			trace("database file = " + _dbFile.nativePath);
		}
		
		public function get name(): String {
			return _dbName;
		}
		
		public function create(): void {
			
		}
		
		public function open(): void {
			_dbConnection.addEventListener(SQLEvent.OPEN, openHandler);
			_dbConnection.addEventListener(SQLErrorEvent.ERROR, handleSQLError);			
			_dbConnection.open(_dbFile);
		}
		
		public function close(): void {
			_dbConnection.close();
		}
		
		public function getSQLStatement(): SQLStatement {
			var stmt:SQLStatement = new SQLStatement();
			return stmt;
		}

		public function executeSQL(sql: String): void {
			trace("execute SQL: '" + sql + "'");
			var stmt:SQLStatement = getSQLStatement();
			stmt.text = sql;
			
			// register listeners for the result and error events
			stmt.addEventListener(SQLEvent.RESULT, handleSQLSuccess);
			stmt.addEventListener(SQLErrorEvent.ERROR, handleSQLError);

			_statementQueue.addItem(stmt);
			if (_isOpen) {			
				processQueuedStatements();
			}
		}

		function openHandler(event:SQLEvent): void {
			trace("database '" + this.name + "' was opened successfully");
			_isOpen = true;
			var dbe:LocalDatabaseEvent = new LocalDatabaseEvent(LocalDatabaseEvent.DATABASE_OPENED, this);
			dispatchEvent(dbe);	
			processQueuedStatements();
		}
		
		private function processQueuedStatements(): void {
			while (_statementQueue.length > 0) {
				processNextQueuedStatement();
			}
		}
		
		private function processNextQueuedStatement(): void {
			var stmt:SQLStatement = consumeQueuedStatement();
			if (stmt != null) {
				trace("process queued SQLStatement: " + stmt.text);
				stmt.sqlConnection = _dbConnection;
				_lastSQLStatement = stmt;
				if (stmt.text.indexOf("SELECT")>=0) {
					stmt.execute(-1, new Responder(handleResult));
				}
				else if (stmt.text.indexOf("INSERT")>=0) {
					stmt.execute(-1, new Responder(handleResult));
				}
				else {
					stmt.execute();
				}
			}
		}
		
		private function consumeQueuedStatement(): SQLStatement {
			if (_statementQueue == null || _statementQueue.length == 0) {
				trace("ERROR: no statement to consume");
				return null;
			}
			var stmt:SQLStatement = _statementQueue[0];
			_statementQueue.removeItemAt(0);
			return stmt;
		}
		
		function handleSQLError(event:SQLErrorEvent): void {
			var stmt:SQLStatement = event.target as SQLStatement;
			trace("QUERY FAILED: SQL Statement: " + stmt.text);
			trace("Error ID:", event.error.errorID);
			trace("Details:", event.error.message);
			
			var dbe:LocalDatabaseEvent = new LocalDatabaseEvent(LocalDatabaseEvent.DATABASE_SQL_FAILED, this);
			dbe.sqlStatement = stmt;
			dispatchEvent(dbe);		
		}
		
		function handleSQLSuccess(event:SQLEvent): void {
			if (event.target != null) {
				var stmt:SQLStatement = event.target as SQLStatement;
				trace("QUERY SUCCEEDED: SQL Statement: " + stmt.text);
			}
		}
		
		function handleResult(result:SQLResult): void {
			if (result!=null) { 
				trace("query result: " + result);
			}
			var dbe:LocalDatabaseEvent = new LocalDatabaseEvent(LocalDatabaseEvent.DATABASE_SQL_EXECUTED, this);
			dbe.sqlStatement = _lastSQLStatement;
			dbe.result = result;
			dispatchEvent(dbe);		
			processNextQueuedStatement();	
		}
		
		private var _dbConnection:SQLConnection;
		private var _dbFile:File;
		private var _dbName: String;
		private var _isOpen: Boolean = false;
		private var _statementQueue: ArrayCollection;
		private var _lastSQLStatement:SQLStatement;

	}
}